from sqlalchemy import Column, Integer, String, DateTime, ForeignKey, create_engine, Index, Text, Boolean, Float
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
from datetime import datetime
import os
import hashlib

# 数据库配置
DATABASE_URL = os.getenv("DATABASE_URL", "mysql+pymysql://root:12345@localhost:3306/redbook")

# 创建数据库引擎
engine = create_engine(DATABASE_URL, echo=True)

# 创建会话
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

# 创建基类
Base = declarative_base()

# 用户模型
class User(Base):
    __tablename__ = "users"
    
    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    phone = Column(String(11), unique=True, index=True, nullable=True, comment="手机号")
    username = Column(String(50), unique=True, index=True, nullable=True, comment="用户名")
    password_hash = Column(String(128), nullable=True, comment="密码哈希")
    nickname = Column(String(50), nullable=False, comment="昵称")
    created_at = Column(DateTime, default=datetime.utcnow, comment="创建时间")
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, comment="更新时间")
    
    def set_password(self, password: str):
        """设置密码（加密存储）"""
        self.password_hash = hashlib.sha256(password.encode()).hexdigest()
    
    def check_password(self, password: str) -> bool:
        """验证密码"""
        return self.password_hash == hashlib.sha256(password.encode()).hexdigest()

# 标签表
class Tag(Base):
    __tablename__ = "tags"
    
    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    name = Column(String(30), unique=True, nullable=False, comment="标签名称")
    icon = Column(String(255), nullable=False, comment="标签图标URL")
    hot = Column(Integer, default=0, comment="热度值")
    parent_id = Column(Integer, ForeignKey('tags.id'), nullable=True, comment="父标签ID")
    created_at = Column(DateTime, default=datetime.utcnow, comment="创建时间")
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, comment="更新时间")
    
    # 关系
    parent = relationship("Tag", remote_side=[id], backref="children")
    user_tags = relationship("UserTag", back_populates="tag")

# 用户标签关联表
class UserTag(Base):
    __tablename__ = "user_tags"
    
    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey('users.id'), nullable=False, comment="用户ID")
    tag_id = Column(Integer, ForeignKey('tags.id'), nullable=False, comment="标签ID")
    created_at = Column(DateTime, default=datetime.utcnow, comment="选择时间")
    
    # 关系
    user = relationship("User", backref="user_tags")
    tag = relationship("Tag", back_populates="user_tags")
    
    # 联合唯一索引
    __table_args__ = (
        Index('idx_user_tag_unique', 'user_id', 'tag_id', unique=True),
    )

# 内容表（笔记）
class Content(Base):
    __tablename__ = "contents"
    
    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    title = Column(String(200), nullable=False, comment="笔记标题")
    description = Column(Text, nullable=True, comment="笔记描述")
    author_id = Column(Integer, ForeignKey('users.id'), nullable=False, comment="作者ID")
    status = Column(Integer, default=1, comment="状态：1=草稿，2=已发布，3=已删除")
    view_count = Column(Integer, default=0, comment="浏览次数")
    like_count = Column(Integer, default=0, comment="点赞数")
    comment_count = Column(Integer, default=0, comment="评论数")
    share_count = Column(Integer, default=0, comment="分享数")
    collect_count = Column(Integer, default=0, comment="收藏数")
    is_top = Column(Boolean, default=False, comment="是否置顶")
    created_at = Column(DateTime, default=datetime.utcnow, comment="创建时间")
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, comment="更新时间")
    published_at = Column(DateTime, nullable=True, comment="发布时间")
    
    # 关系
    author = relationship("User", backref="contents")
    content_tags = relationship("ContentTag", back_populates="content")
    content_media = relationship("ContentMedia", back_populates="content")
    
    # 索引
    __table_args__ = (
        Index('idx_content_status', 'status'),
        Index('idx_content_author', 'author_id'),
        Index('idx_content_published', 'published_at'),
        Index('idx_content_hot', 'like_count', 'comment_count', 'share_count'),
    )

# 内容标签关联表
class ContentTag(Base):
    __tablename__ = "content_tags"
    
    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    content_id = Column(Integer, ForeignKey('contents.id'), nullable=False, comment="内容ID")
    tag_id = Column(Integer, ForeignKey('tags.id'), nullable=False, comment="标签ID")
    created_at = Column(DateTime, default=datetime.utcnow, comment="关联时间")
    
    # 关系
    content = relationship("Content", back_populates="content_tags")
    tag = relationship("Tag", backref="content_tags")
    
    # 联合唯一索引，防止重复标签
    __table_args__ = (
        Index('idx_content_tag_unique', 'content_id', 'tag_id', unique=True),
        Index('idx_content_tag_content', 'content_id'),
        Index('idx_content_tag_tag', 'tag_id'),
    )

# 内容媒体表（图片/视频）
class ContentMedia(Base):
    __tablename__ = "content_media"
    
    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    content_id = Column(Integer, ForeignKey('contents.id'), nullable=False, comment="内容ID")
    media_url = Column(String(500), nullable=False, comment="媒体文件URL")
    media_type = Column(String(20), nullable=False, comment="媒体类型：image/video")
    sort_order = Column(Integer, default=1, comment="排序，1表示首图")
    width = Column(Integer, nullable=True, comment="宽度")
    height = Column(Integer, nullable=True, comment="高度")
    file_size = Column(Integer, nullable=True, comment="文件大小（字节）")
    duration = Column(Integer, nullable=True, comment="视频时长（秒）")
    created_at = Column(DateTime, default=datetime.utcnow, comment="创建时间")
    
    # 关系
    content = relationship("Content", back_populates="content_media")
    
    # 索引
    __table_args__ = (
        Index('idx_content_media_content', 'content_id'),
        Index('idx_content_media_type', 'media_type'),
        Index('idx_content_media_sort', 'content_id', 'sort_order'),
    )

# 内容地理位置表
class ContentLocation(Base):
    __tablename__ = "content_locations"
    
    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    content_id = Column(Integer, ForeignKey('contents.id'), nullable=False, comment="内容ID")
    latitude = Column(Float, nullable=False, comment="纬度")
    longitude = Column(Float, nullable=False, comment="经度")
    city = Column(String(50), nullable=True, comment="城市")
    district = Column(String(100), nullable=True, comment="区县")
    address = Column(String(200), nullable=True, comment="详细地址")
    location_precision = Column(String(20), default="city", comment="定位精度：city/district/address")
    created_at = Column(DateTime, default=datetime.utcnow, comment="创建时间")
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, comment="更新时间")
    
    # 关系
    content = relationship("Content", backref="location")
    
    # 索引
    __table_args__ = (
        Index('idx_content_location_content', 'content_id'),
        Index('idx_content_location_city', 'city'),
        Index('idx_content_location_coord', 'latitude', 'longitude'),
    )

# 创建所有表
def create_tables():
    try:
        Base.metadata.create_all(bind=engine)
    except Exception as e:
        if "Unknown database" in str(e):
            # 数据库不存在，先创建数据库
            print("数据库不存在，正在创建...")
            create_database()
            # 重新创建引擎和表
            new_engine = create_engine(DATABASE_URL, echo=True)
            Base.metadata.create_all(bind=new_engine)
        else:
            raise e

def create_database():
    """创建数据库"""
    # 从DATABASE_URL解析数据库信息
    db_url = DATABASE_URL.replace("mysql+pymysql://", "")
    parts = db_url.split("@")
    user_pass = parts[0].split(":")
    host_port_db = parts[1].split("/")
    host_port = host_port_db[0].split(":")
    
    username = user_pass[0]
    password = user_pass[1]
    host = host_port[0]
    port = int(host_port[1]) if len(host_port) > 1 else 3306
    database = host_port_db[1]
    
    import pymysql
    try:
        # 连接MySQL服务器（不指定数据库）
        connection = pymysql.connect(
            host=host,
            port=port,
            user=username,
            password=password,
            charset='utf8mb4'
        )
        
        with connection.cursor() as cursor:
            # 创建数据库
            cursor.execute(f"CREATE DATABASE IF NOT EXISTS {database} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci")
            print(f"数据库 {database} 创建成功")
            
        connection.close()
        
    except Exception as e:
        print(f"创建数据库失败: {e}")
        raise e

# 初始化标签数据
def init_tags():
    """初始化预设标签数据"""
    db = SessionLocal()
    try:
        # 检查是否已有标签数据
        if db.query(Tag).count() > 0:
            print("标签数据已存在，跳过初始化")
            return
        
        # 预设标签数据
        tags_data = [
            {"name": "美食", "icon": "https://img.icons8.com/color/96/000000/food.png"},
            {"name": "旅行", "icon": "https://img.icons8.com/color/96/000000/travel.png"},
            {"name": "美妆", "icon": "https://img.icons8.com/color/96/000000/makeup.png"},
            {"name": "穿搭", "icon": "https://img.icons8.com/color/96/000000/fashion.png"},
            {"name": "健身", "icon": "https://img.icons8.com/color/96/000000/fitness.png"},
            {"name": "摄影", "icon": "https://img.icons8.com/color/96/000000/camera.png"},
            {"name": "读书", "icon": "https://img.icons8.com/color/96/000000/book.png"},
            {"name": "音乐", "icon": "https://img.icons8.com/color/96/000000/music.png"},
            {"name": "电影", "icon": "https://img.icons8.com/color/96/000000/movie.png"},
            {"name": "游戏", "icon": "https://img.icons8.com/color/96/000000/game.png"},
            {"name": "咖啡", "icon": "https://img.icons8.com/color/96/000000/coffee.png"},
            {"name": "宠物", "icon": "https://img.icons8.com/color/96/000000/pet.png"},
            {"name": "手工", "icon": "https://img.icons8.com/color/96/000000/craft.png"},
            {"name": "科技", "icon": "https://img.icons8.com/color/96/000000/technology.png"},
            {"name": "家居", "icon": "https://img.icons8.com/color/96/000000/home.png"},
            {"name": "运动", "icon": "https://img.icons8.com/color/96/000000/sports.png"},
            {"name": "艺术", "icon": "https://img.icons8.com/color/96/000000/art.png"},
            {"name": "学习", "icon": "https://img.icons8.com/color/96/000000/study.png"},
            {"name": "购物", "icon": "https://img.icons8.com/color/96/000000/shopping.png"},
            {"name": "情感", "icon": "https://img.icons8.com/color/96/000000/heart.png"}
        ]
        
        # 批量插入标签
        for tag_data in tags_data:
            tag = Tag(**tag_data)
            db.add(tag)
        
        db.commit()
        print(f"成功初始化 {len(tags_data)} 个标签")
        
    except Exception as e:
        print(f"初始化标签数据失败: {e}")
        db.rollback()
    finally:
        db.close()

# 获取数据库会话
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()
